﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using PowerCreator.LMS.Common;
using PowerCreator.LMS.Common.Utils;
using PowerCreator.LMS.Common.Script;
using System.Text;
using PowerCreator.LMS.User.Entity;

public partial class sysadmin_UserManager_AddUserInfo_In : PowerCreator.LMS.Web.Base.BasePage
{
    PowerCreator.LMS.Web.User.Department objDepartment = new PowerCreator.LMS.Web.User.Department();
    PowerCreator.LMS.User.Entity.VOTDepartment objDepartmentInfo = new VOTDepartment();

    PowerCreator.LMS.Web.User.User objUser = new PowerCreator.LMS.Web.User.User();
    //LogicServer.NetMeetingClass objNetMeetingClass = new LogicServer.NetMeetingClass();

    private string ExcelName_New = string.Empty;//实际文件名称
    private string strFileName = string.Empty;//文件格式
    private string error = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            InitPage();
            ViewState["UserType"]=Request.QueryString["UserType"].ToString();

            PowerCreator.LMS.Web.Project.ProjectUser obj = new PowerCreator.LMS.Web.Project.ProjectUser();
            dpdlst_Class.DataSource = obj.QueryProjectClass("TrainclUserType=1");
            dpdlst_Class.DataTextField = "TrainclaName";
            dpdlst_Class.DataValueField = "TrainclaID";
            dpdlst_Class.DataBind();
            
            

        }
    }
    
    protected override void InitPage()
    {
        lbl_InfoTitle_EditorJob.Text = GetLanageValueByKey("UserExportByExcel_TitleInfo", PowerCreator.LMS.Language.LanagetItemType.Page);
        Label1.Text = GetLanageValueByKey("UserExportByExcel_IsToClass", PowerCreator.LMS.Language.LanagetItemType.Page);
        lbl_FileName.Text = GetLanageValueByKey("UserExportByExcel_SelClass", PowerCreator.LMS.Language.LanagetItemType.Page);
        chkbx_ok.Text = GetLanageValueByKey("UserExportByExcel_Export", PowerCreator.LMS.Language.LanagetItemType.Page);
        btnSave.Text = GetLanageValueByKey("UserExportByExcel_Export2", PowerCreator.LMS.Language.LanagetItemType.Page);
        btn_DownLoad.Text = GetLanageValueByKey("UserExportByExcel_Doc", PowerCreator.LMS.Language.LanagetItemType.Page);
        lab_msg.Text = GetLanageValueByKey("UserExportByExcel_Msg", PowerCreator.LMS.Language.LanagetItemType.Page);

        base.InitPage();
    }


    protected void btnSave_Click(object sender, EventArgs e)
    {

        
        string GeneralPath = Server.MapPath(WebUtils.WebRoot + "//General.config");
        lab_msg.Visible = true;
        StringBuilder str_msg = new StringBuilder();
       
        if (!UploadFile())
        {
            str_msg.Append(GetLanageValueByKey("UserExportByExcel_Msg1", PowerCreator.LMS.Language.LanagetItemType.Page) + error);
            lab_msg.Text = GetLanageValueByKey("UserExportByExcel_Msg2", PowerCreator.LMS.Language.LanagetItemType.Page) + str_msg.ToString();
            return;
        }

        string msg = GetExcelDate(ExcelName_New, GeneralPath);

        lab_msg.Text = GetLanageValueByKey("UserExportByExcel_Msg2", PowerCreator.LMS.Language.LanagetItemType.Page) + str_msg.ToString() + msg;
     
    }

    //上传软件
    bool UploadFile()
    {
        if (File1.PostedFile.FileName.Length < 1)
        {
            Script.Alert(Page, "msg", GetLanageValueByKey("UserExportByExcel_Msg3", PowerCreator.LMS.Language.LanagetItemType.Page));
            return false;
        }
        string strName = this.File1.PostedFile.FileName.ToString();
        int nlength = strName.Length - strName.LastIndexOf("\\") - 1;
        string ImgName = strName.Substring(strName.LastIndexOf("\\") + 1, nlength);
        //获取软件格式
        strFileName = strName.Substring(strName.LastIndexOf(".")).Replace(".", "");
        if (strFileName.ToLower() != "xls")
        {
            error = GetLanageValueByKey("UserExportByExcel_Msg4", PowerCreator.LMS.Language.LanagetItemType.Page);
            return false;
        }

        ExcelName_New = Guid.NewGuid() + "." + strFileName.ToLower();
        string sPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + "QuestionDocument\\" + ExcelName_New;
        //string sPath = Server.MapPath(Utils.WebRoot + "\\QuestionDocument\\" + ExcelName_New + ".word"); +"QuestionDocument\\" + ExcelName_New;
        try
        {
            this.File1.PostedFile.SaveAs(sPath);
            return true;
        }
        catch(Exception ex)
        {
            error = ex.Message;
            return false;
        }

    }
    protected void btn_DownLoad_Click(object sender, EventArgs e)
    {
        Script.RedirectURL(WebUtils.WebRoot + "/QuestionDocument/AddUser_Demo.xls");
    }

    //SQL方式读取
    public DataTable GetExcelDateBySQL(string newFileName, ref string eror, string GeneralPath)
    {

        DataTable dt = new DataTable();
        string filePath = Server.MapPath(WebUtils.WebRoot + "\\QuestionDocument\\" + newFileName);
        //private string GeneralFilePath=
        string conn = PowerCreator.LMS.Data.XML.GeneralSetting.GetSetting(GeneralPath)["GetExcelConnectionstring"];

        //SqlConnection connection = new SqlConnection("server=CHINESE-296470C\\POWERCREATOR;uid=sa;pwd=123456;database=master;");
        SqlConnection connection = new SqlConnection(conn);
        //string cmd = "select * from  OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\\lms\\1defff82-9555-4e06-9151-ef08f9881ba1.xls',sheet1$)";
        string cmd = string.Format(System.Configuration.ConfigurationManager.AppSettings["SelectExcelCmd"].ToString(), filePath);
        SqlDataAdapter da = new SqlDataAdapter(cmd, connection);
        DataSet ds = new DataSet();
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            eror = ex.Message;
            return null;
        }
        dt = ds.Tables[0];
        return dt;
    }


    public string GetExcelDate(string newFileName, string GeneralPath)
    {
        StringBuilder msg = new StringBuilder();
        string str_eror = string.Empty;
        DataTable dt_Excel = new DataTable();


        string AddUserType = System.Configuration.ConfigurationManager.AppSettings["AddUserType"].ToString();
        if (AddUserType == "Sqlserver")
        {
            dt_Excel = GetExcelDateBySQL(newFileName, ref str_eror, GeneralPath);
            msg.Append(str_eror);
        }
        else if (AddUserType == "Oledb")
        {
            string filePath = Server.MapPath(WebUtils.WebRoot + "\\QuestionDocument\\" + newFileName);
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            string cmd = "select * from [Sheet1$]";
            OleDbDataAdapter da = new OleDbDataAdapter(cmd, strConn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt_Excel = ds.Tables[0];
            msg.Append(str_eror);
        }
        //else if (AddUserType == "Dcom")
        //{
        //    return GetExcelDateByDCOM(newFileName);

        //}

        //string filePath = Server.MapPath(Utils.WebRoot + "\\QuestionDocument\\" + newFileName);
        //dt_Excel = objexcel.ExcelSheetName(filePath, ref str_eror);
        //msg.Append(str_eror);


        if (dt_Excel == null)
        {
            msg.Append(GetLanageValueByKey("UserExportByExcel_Msg5", PowerCreator.LMS.Language.LanagetItemType.Page));
            return msg.ToString();
        }
        if (dt_Excel.Rows.Count == 0)
        {

            msg.Append(GetLanageValueByKey("UserExportByExcel_Msg5", PowerCreator.LMS.Language.LanagetItemType.Page));
            return msg.ToString();
        }
        int count = 0;
        try
        {


            for (int i = 0; i < dt_Excel.Rows.Count; i++)
            {
                count += 1;

                PowerCreator.LMS.User.Entity.VOTUser objUserInfo = new VOTUser();
                PowerCreator.LMS.User.Entity.VOTStudent objStudentInfo = new VOTStudent();


                objUserInfo.UserName = dt_Excel.Rows[i]["帐户"].ToString();
                if (objUserInfo.UserName == "")
                {
                    msg.Append("第" + count.ToString() + "行记录：帐户信息有误。" + "</br>");
                    continue;
                }
                //string spwd = dt_Excel.Rows[i]["密码"].ToString();
                //objUserInfo.UserPassword = MD5Helper.GetMD5(dt_Excel.Rows[i]["密码"].ToString());
                objUserInfo.UserPassword = dt_Excel.Rows[i]["密码"].ToString();
                objUserInfo.UserLoginIP = dt_Excel.Rows[i]["登陆IP"].ToString();
                if (objUserInfo.UserPassword == "")
                {
                    msg.Append("第" + count.ToString() + "行记录：密码信息有误。" + "</br>");
                    continue;
                }
                if (ViewState["UserType"].ToString() == "0")   //添加学生信息
                {

                    if (dt_Excel.Rows[i]["电子邮箱"].ToString().Trim() != "")
                    {
                        objStudentInfo.StudentEmail = dt_Excel.Rows[i]["电子邮箱"].ToString();
                        //if (objStudentInfo.StudentEmail == "")
                        //{
                        //    msg.Append("第" + count.ToString() + "行记录：电子邮箱信息有误。" + "</br>");
                        //    continue;
                        //}
                    }

                    objStudentInfo.StudentName = dt_Excel.Rows[i]["真实姓名"].ToString();

                    objStudentInfo.StudentHomeplace = dt_Excel.Rows[i]["籍贯"].ToString();

                    objStudentInfo.StudentAddress = dt_Excel.Rows[i]["地址"].ToString();
                    objStudentInfo.StudentPost = dt_Excel.Rows[i]["邮编"].ToString();
                    if (dt_Excel.Rows[i]["生日"].ToString().Trim() != "")
                    {
                        string birthday = dt_Excel.Rows[i]["生日"].ToString();

                        try
                        {
                            objStudentInfo.StudentBirthday = Convert.ToDateTime(birthday);
                        }
                        catch
                        {
                            msg.Append("第" + count.ToString() + "行记录：生日信息错误。" + "</br>");
                            continue;
                        }
                    }
                    if (dt_Excel.Rows[i]["性别"].ToString().Trim() != "")
                    {
                        string sex = dt_Excel.Rows[i]["性别"].ToString().Trim();
                        try
                        {
                            if (dt_Excel.Rows[i]["性别"].ToString().Trim() == "女")
                                objStudentInfo.StudentSex = 1;
                            else if (dt_Excel.Rows[i]["性别"].ToString().Trim() == "男")
                                objStudentInfo.StudentSex = 0;
                            else
                                objStudentInfo.StudentSex = 0;
                        }
                        catch
                        {
                            msg.Append("第" + count.ToString() + "行记录：性别信息错误。" + "</br>");
                            continue;
                        }
                    }
                    string departName = dt_Excel.Rows[i]["部门名称"].ToString().Trim();
                    if (departName == "")
                    {
                        msg.Append("第" + count.ToString() + "行记录：部门信息数据不完整。" + "</br>");
                        continue;
                    }
                    DataTable dt_depart = objDepartment.Query("DepartmentType=2 and DepartmentName='" + departName + "'");
                    if (dt_depart.Rows.Count > 0)
                    {
                        objStudentInfo.FKStudentDepartmentId = int.Parse(dt_depart.Rows[0]["DepartmentId"].ToString());
                    }
                    else
                    {
                        msg.Append("第" + count.ToString() + "行记录：部门信息错误。" + "</br>");
                        continue;
                    }

                    objStudentInfo.StudentMobileTelephone = dt_Excel.Rows[i]["手机号码"].ToString();

                    objStudentInfo.StudentDescription = dt_Excel.Rows[i]["备注信息"].ToString();

                }
                else      //添加老师信息
                {
                    //PowerCreator.LMS.User.Entity.VOTTeacher objTeacher = new VOTTeacher();

                    //string departName = dt_Excel.Rows[i]["部门名称"].ToString();
                    //if (departName == "")
                    //{
                    //    msg.Append("第" + count.ToString() + "行记录：部门信息数据不完整。" + "</br>");
                    //    continue;
                    //}
                    //DataTable dt_depart = objDepartment.Query("DepartmentType=1 and DepartmentName='" + departName + "'");
                    //if (dt_depart.Rows.Count > 0)
                    //{
                    //    objTeacher.FKStudentDepartmentId = int.Parse(dt_depart.Rows[0]["DepartmentId"].ToString());
                    //}
                    //else
                    //{
                    //    msg.Append("第" + count.ToString() + "行记录：部门信息错误。" + "</br>");
                    //    continue;
                    //}
                 
                    
                    
                }
                

                //objSecurityUserInfo.EmployeCode = dt_Excel.Rows[i]["员工代码"].ToString();
                //if (objSecurityUserInfo.EmployeCode == "")
                //{
                //    msg.Append("第" + count.ToString() + "行记录：员工代码信息有误。" + "</br>");
                //    continue;
                //}

               
                //if (objStudentInfo.StudentName == "")
                //{
                //    msg.Append("第" + count.ToString() + "行记录：真实姓名信息有误。" + "</br>");
                //    continue;
                //}

                //if (!string.IsNullOrEmpty(dt_Excel.Rows[i]["年龄"].ToString()))
                //{
                //    objSecurityUserInfo.Age = int.Parse(dt_Excel.Rows[i]["年龄"].ToString());

                //}
                //else
                //{
                //    msg.Append("第" + count.ToString() + "行记录：年龄信息有误。" + "</br>");
                //    continue;
                //}
               
                //string Identity = dt_Excel.Rows[i]["身份证号码"].ToString();
                //if (PowerCreator.LMS.Common.Utils.PageValidate.(Identity))
                //{
                //    objSecurityUserInfo.Identity = Identity;
                //}
                
                //string Telephone = dt_Excel.Rows[i]["电话"].ToString();
                //if (PageValidate.IsPhone(Telephone))
                //{
                //    objSecurityUserInfo.Telephone = Telephone;
                //}

               
                //objSecurityUserInfo.Education = dt_Excel.Rows[i]["学历"].ToString();
                //objSecurityUserInfo.SchoolName = dt_Excel.Rows[i]["毕业院校"].ToString();
               

                //string JobName = dt_Excel.Rows[i]["职位"].ToString();
                //if (JobName == "")
                //{
                //    msg.Append("第" + count.ToString() + "行记录：职位信息数据不完整。" + "</br>");
                //    continue;
                //}
                //DataTable dt_JobInfo = objManagerJob.Query("JobName= '" + JobName + "'");
                //if (dt_JobInfo.Rows.Count > 0)
                //{
                //    objSecurityUserInfo.JobID = int.Parse(dt_JobInfo.Rows[0]["JobID"].ToString());

                //}
                //else
                //{
                //    msg.Append("第" + count.ToString() + "行记录：职位信息错误。" + "</br>");
                //    continue;
                //}
                //string DutyName = dt_Excel.Rows[i]["职称"].ToString();
                //if (DutyName == "")
                //{
                //    msg.Append("第" + count.ToString() + "行记录：职称信息数据不完整。" + "</br>");
                //    continue;
                //}
                //DataTable dt_DutyInfo = objManagerDuty.Query("DutyName='" + DutyName + "'");
                //if (dt_DutyInfo.Rows.Count > 0)
                //{
                //    objSecurityUserInfo.DutyID = int.Parse(dt_DutyInfo.Rows[0]["DutyID"].ToString());
                //}
                //else
                //{
                //    msg.Append("第" + count.ToString() + "行记录：职称信息错误。" + "</br>");
                //    continue;
                //}
                //string workstart = dt_Excel.Rows[i]["入职时间"].ToString();
                //if (PageValidate.IsDateTime(workstart))
                //{
                //    objSecurityUserInfo.WorkStartingDate = Convert.ToDateTime(workstart);
                //}
                
                //objSecurityUserInfo.RoleIDList = dt_Excel.Rows[i]["RoleIDList"].ToString();

                if (dt_Excel.Rows[i]["审核状态"].ToString().Trim() != "")
                {
                    if (dt_Excel.Rows[i]["审核状态"].ToString() == "是")
                    {
                        objUserInfo.UserIscheck = 1;
                    }
                    else
                    {
                        objUserInfo.UserIscheck = 0;
                    }

                }
                else
                {
                    objUserInfo.UserIscheck = 1;
                }
                

                objUserInfo.UserTotalLoginNum = 0;
                objUserInfo.UserTotalLoginTime = 0;
                objUserInfo.UserType = 0;

                if (objUser.Query("UserName='" + objUserInfo.UserName + "'").Rows.Count > 0)
                {
                    msg.Append("第" + count.ToString() + "行记录：帐户已存在。" + "</br>");

                    continue;
                }
                 PowerCreator.LMS.Web.CreatedByInfo objCreatedByInfo=new PowerCreator.LMS.Web.CreatedByInfo();

                 int UserID = 0;
                 //导入到方案班级
                 if (chkbx_ok.Checked)
                 {
                     int FKTrainclastuTrainclaID = int.Parse(dpdlst_Class.SelectedItem.Value);
                     PowerCreator.LMS.Project.Entity.VOTTrainClassStudent objTrainClassStudentInfo = new PowerCreator.LMS.Project.Entity.VOTTrainClassStudent();
                     objTrainClassStudentInfo.FKTrainclastuTrainclaID = FKTrainclastuTrainclaID;
                     objTrainClassStudentInfo.TrainclastuCreatetime = DateTime.Now;
                     objTrainClassStudentInfo.FKTrainclastuUserID = GetLoginEntity.UserID;
                     objTrainClassStudentInfo.TrainclastuCreatorname = GetLoginEntity.UserName;

                     if (!objUser.AddStudent(objUserInfo, objStudentInfo, GetCreatedByInfo(), objTrainClassStudentInfo,ref UserID))
                     {

                         msg.Append("第" + count.ToString() + "行记录：导入失败。" + "</br>");

                     }
                     else
                     {
                         //objNetMeetingClass.WebAddUser(UserID, objUserInfo.UserName, objUserInfo.UserPassword, (ulong)objUserInfo.UserRule, objUserInfo.UserImageIndex, objStudentInfo.StudentDescription, objStudentInfo.StudentSex, objStudentInfo.StudentTelephone, objUserInfo.UserType);
                         msg.Append("第" + count.ToString() + "行记录：导入成功。" + "</br>");

                     }
                 }
                 else
                 {
                     
                     if (!objUser.AddStudent(objUserInfo, objStudentInfo, GetCreatedByInfo(),ref UserID))
                     {
                         msg.Append("第" + count.ToString() + "行记录：导入失败。" + "</br>");

                     }
                     else
                     {
                         //objNetMeetingClass.WebAddUser(UserID, objUserInfo.UserName, objUserInfo.UserPassword, (ulong)objUserInfo.UserRule, objUserInfo.UserImageIndex, objStudentInfo.StudentDescription, objStudentInfo.StudentSex, objStudentInfo.StudentTelephone, objUserInfo.UserType);
                         msg.Append("第" + count.ToString() + "行记录：导入成功。" + "</br>");

                     }
                 }
                
          


            }


        }
        catch (Exception ex)
        {
            msg.Append("第" + count.ToString() + "行记录：导入失败。" + "(" + ex.Message + ")" + "</br>");
        }
        return msg.ToString();
    }


    protected void chkbx_ok_CheckedChanged(object sender, EventArgs e)
    {
        if (chkbx_ok.Checked)
        {
            dpdlst_Class.Visible = true;
        }
        else
        {
            dpdlst_Class.Visible = false;
        }
    }
}
